package com.liu.io.file.excel.poi;

import com.liu.io.FileUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 操作xls文件
 *
 * @author liubo
 */
public class HSSFWorkbookUtil {
    /**
     * 创建Xls
     *
     * @param data
     */
    public void writeXls(Map<String, Object> data, String name) throws IOException {
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook workbook = new HSSFWorkbook();

        for (Map.Entry<String, Object> dataEntry : data.entrySet()) {
            List<String> thead = new LinkedList<String>();
            String sheetName = "";
            List<List<String>> lists = new LinkedList<List<String>>();
            List<List<CellType>> cellTypes = new LinkedList<List<CellType>>();
            List<Map<String, Object>> list = (List<Map<String, Object>>) dataEntry.getValue();
            for (Map<String, Object> map : list) {
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    if ("THEAD".equals(entry.getKey())) {
                        thead = (List<String>) entry.getValue();
                    } else if ("SHEET_NAME".equals(entry.getKey())) {
                        sheetName = (String) entry.getValue();
                    } else if ("COLUMN".equals(entry.getKey())) {
                        List<String> row = (List<String>) entry.getValue();
                        lists.add(row);
                    } else if ("CELL_TYPE".equals(entry.getKey())) {
                        List<CellType> cellType = (List<CellType>) entry.getValue();
                        cellTypes.add(cellType);
                    } else {

                    }
//                System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
                }
            }
            workbook = this.createXls(workbook, thead, sheetName, lists, cellTypes);
        }

        File file = FileUtil.initFile(name);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        workbook.write(fileOutputStream);
        fileOutputStream.flush();
    }

    public HSSFWorkbook createXls(HSSFWorkbook workbook, List<String> thead, String sheetName, List<List<String>> lists, List<List<CellType>> cellTypes) {
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFRow row = null;
        HSSFCell cell = null;

        //创建表头
        row = sheet.createRow(0);
        for (int i = 0; i < thead.size(); i++) {
            // 创建单元格
            cell = row.createCell(i);
            // 单元格类型
            CellType cellType = cellTypes.get(0).get(i);
            if (cellType != null) {
                cell.setCellType(cellType);
            }
            // 单元格内容
            cell.setCellValue(thead.get(i));
        }

        //表格赋值
        for (int n = 0; n < lists.size(); n++) {
            List<String> list = lists.get(n);
            int rowIndex = n + 1;
            List<CellType> cellType = cellTypes.get(rowIndex);
            row = sheet.createRow(rowIndex);
            for (int i = 0; i < list.size(); i++) {
                // 创建单元格
                cell = row.createCell(i);
                // 单元格类型
                CellType type = cellType.get(i);
                if (type != null) {
                    cell.setCellType(type);
                }
                // 单元格内容
                cell.setCellValue(list.get(i));
            }
        }
        return workbook;
    }

    /**
     * 读取xls文件
     *
     * @param file
     * @return
     */
    public Map<String, Object> readXls(File file, int[] sheets) throws IOException {
        Map<String, Object> map = new HashMap<String, Object>();
        InputStream in = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(in);

        for (int i = 0; i < sheets.length; i++) {
            int sheetIndex = sheets[i];
            map.put(String.valueOf(sheetIndex), this.getData(workbook, sheetIndex));
        }
        return map;
    }


    public List<Map<String, Object>> getData(HSSFWorkbook workbook, int sheetIndex) {
        List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();

        // 获得excel文件中的某个sheet项
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        // 获得sheet对象中的所有行记录
        int rowSize = sheet.getPhysicalNumberOfRows();

        //表格头
        List<String> thead = new LinkedList<String>();
        for (int rowIndex = 0; rowIndex <= rowSize; rowIndex++) {
            Map<String, Object> map = new HashMap<String, Object>();

            //获取第i行
            HSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            if (rowIndex == 0) {
                List<CellType> cellType = new LinkedList<CellType>();
                //第一行设置表格头
                for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                    String data = "";
                    CellType type = null;
                    //列
                    HSSFCell cell = row.getCell(columnIndex);
                    //单元格
                    if (cell != null) {
                        data = this.getCellData(cell);
                        type = cell.getCellType();
                    }
                    thead.add(data);
                    cellType.add(type);
                }
                map.put("THEAD", thead);
                map.put("SHEET_NAME", sheet.getSheetName());
                map.put("CELL_TYPE", cellType);

            } else {
                List<String> column = new LinkedList<String>();
                List<CellType> cellType = new LinkedList<CellType>();
                for (short columnIndex = 0; columnIndex <= thead.size(); columnIndex++) {
                    String data = "";
                    CellType type = null;
                    //列
                    HSSFCell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        data = this.getCellData(cell);
                        type = cell.getCellType();
                    }
                    column.add(data);
                    cellType.add(type);
                }
                map.put("COLUMN", column);
                map.put("CELL_TYPE", cellType);
            }
            list.add(map);
        }
        return list;
    }

    public String getCellData(HSSFCell cell) {
        String data = "";
        switch (cell.getCellType()) {
            case STRING:
                //字符串
                data = cell.getStringCellValue();
                break;
            case NUMERIC:
                //数字
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        data = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
                    } else {
                        data = "";
                    }
                } else {
                    data = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                //公式
                // 导入时如果为公式生成的数据则无值
                if (!cell.getStringCellValue().equals("")) {
                    data = cell.getStringCellValue();
                } else {
                    data = cell.getNumericCellValue() + "";
                }
                break;
            case BLANK:
                //空值
                break;
            case ERROR:
                //故障
                break;
            case BOOLEAN:
                //Boolean
                data = (cell.getBooleanCellValue() == true ? "Y" : "N");
                break;
            default:
                data = "";
        }
        return data;
    }

}

/**
 * 常用组件：
 * <p>
 * HSSFWorkbook                      excel的文档对象
 * <p>
 * HSSFSheet                         excel的表单
 * <p>
 * HSSFRow                           excel的行
 * <p>
 * HSSFCell                          excel的格子单元
 * <p>
 * HSSFFont                          excel字体
 * <p>
 * HSSFDataFormat                    日期格式
 * <p>
 * HSSFHeader                        sheet头
 * <p>
 * HSSFFooter                        sheet尾（只有打印的时候才能看到效果）
 * <p>
 * 样式：
 * <p>
 * HSSFCellStyle                       cell样式
 * <p>
 * 辅助操作包括：
 * <p>
 * HSSFDateUtil                        日期
 * <p>
 * HSSFPrintSetup                      打印
 * <p>
 * HSSFErrorConstants                  错误信息表
 */